Imports System.Data.SqlClient
Imports System.Reflection
''' -----------------------------------------------------------------------------
''' Project	 : CObject
''' Class	 : CCollection
''' 
''' -----------------------------------------------------------------------------
''' <summary>
''' Retrieves a single business object or a datatable with business object data  
''' </summary>
''' <remarks>
''' </remarks>
''' <history>
''' </history>
''' -----------------------------------------------------------------------------

Public Class CCollection
    Implements IDisposable

    Private msqlConnection As SqlConnection
    Private moCDataTable As CDataTable
    Private moDataTable As DataTable
    Private moSingleObject As CObject
    Private moCObject As CObject
    Private mbReturnDataTable As Boolean


    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' Use this constructor to return a datatable.
    ''' </summary>
    ''' <param name="CObject">New instance of the business object to create</param>
    ''' <remarks>
    ''' To return the table associated with the business object.
    ''' <code>
    '''         cCollection = New CCollection(New Employee)
    '''         Grid.DataSource = cCollection.Datatable
    ''' </code>
    ''' If the filter returns one object (i.e. you filter on the unique key of the table) you will receive back the populated 
    ''' business object.
    ''' </remarks>
    ''' <history>

    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Sub New(ByVal CObject As CObject, Optional ByVal ReturnDataTable As Boolean = False)

        Dim loConn As ConnectionServer.Connection = New ConnectionServer.Connection
        moCObject = CObject
        msqlConnection = CType(loConn.GetConnection(CObject.SourceDataBase), SqlConnection)
        mbReturnDataTable = ReturnDataTable
        moCDataTable = New CDataTable
        MakeDatatable()

    End Sub
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' This contructor will return a datatable or a populated business object.
    ''' </summary>
    ''' <param name="CObject">The object assocociated with the table to be called.</param>
    ''' <param name="psFilter">The filter to apply (The where clause of a SQL statement without the WHERE</param>
    ''' <remarks>
    ''' If you filter to one item you will receive the business object representing that item.  Otherwise you 
    ''' will receive a datatable in a property and an event
    ''' </remarks>
    ''' <history>
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public Sub New(ByVal CObject As CObject, ByVal psFilter As String, Optional ByVal ReturnDataTable As Boolean = False)
        Dim loConn As ConnectionServer.Connection = New ConnectionServer.Connection

        msqlConnection = CType(loConn.GetConnection(CObject.SourceDataBase), SqlConnection)
        mbReturnDataTable = ReturnDataTable
        moCObject = CObject
        MakeSelect(loConn.TestForSQLInjection(psFilter), "")

    End Sub
    Public Sub New(ByVal CObject As CObject, ByVal psFilter As String, ByVal psOrderBy As String, Optional ByVal ReturnDataTable As Boolean = False)
        Dim loConn As ConnectionServer.Connection = New ConnectionServer.Connection

        msqlConnection = CType(loConn.GetConnection(CObject.SourceDataBase), SqlConnection)
        mbReturnDataTable = ReturnDataTable
        moCObject = CObject
        MakeSelect(loConn.TestForSQLInjection(psFilter), psOrderBy)

    End Sub

    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' The returned DataTable
    ''' </summary>
    ''' <returns>Datatable</returns>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public ReadOnly Property CDatatable() As CDataTable
        Get
            Return moCDataTable
        End Get
    End Property
    Public ReadOnly Property DataTable() As DataTable
        Get
            Return moDataTable
        End Get
    End Property
    ''' -----------------------------------------------------------------------------
    ''' <summary>
    ''' The returned populated business object
    ''' </summary>
    ''' <returns>The Cobject based Busines object</returns>
    ''' <remarks>
    ''' </remarks>
    ''' <history>
    ''' </history>
    ''' -----------------------------------------------------------------------------
    Public ReadOnly Property DataObject() As CObject
        Get
            Return moSingleObject
        End Get
    End Property
    Private Sub MakeDatatable(Optional ByVal poFilter As String = "", Optional ByVal poOrderBy As String = "")
        Dim loComm As SqlCommand = GetCommand(poFilter, poOrderBy)

        Dim loADP As SqlDataAdapter = New SqlDataAdapter(loComm)
        Dim loDT As DataTable = New DataTable("Name")
        Dim loObject As CObject
        Dim loRow As DataRow
        Dim loCData As CData
        Dim loColumn As DataColumn
        loComm.CommandTimeout = 300


        loADP.Fill(loDT)


        Do While msqlConnection.State <> ConnectionState.Closed
            msqlConnection.Close()
        Loop
        If mbReturnDataTable Then
            moDataTable = loDT
            Exit Sub
        End If
        moCDataTable = New CDataTable
        For Each loRow In loDT.Rows
            loObject = moCObject.Clone

            For Each loColumn In loDT.Columns
                loCData = New CData

                If IsDBNull(loRow(loColumn.ColumnName)) Or IsNumeric(loRow(loColumn.ColumnName)) Or IsDate(loRow(loColumn.ColumnName)) Then
                    loCData.OldValue = loRow(loColumn.ColumnName)
                ElseIf loColumn.DataType.ToString = "System.Boolean" Then
                    loCData.OldValue = loRow(loColumn.ColumnName)
                Else
                    loCData.OldValue = Trim(CStr(loRow(loColumn.ColumnName)))
                End If
                loCData.NewValue = loCData.OldValue

                loObject.SetProperty(loColumn.ColumnName.ToLower, loCData.OldValue)
 

            Next
            moCDataTable.Add(loObject)
        Next

    End Sub
    Private Function GetCommand(Optional ByVal psFilter As String = "", Optional ByVal psOrderBy As String = "") As SqlCommand
        Dim loComm As SqlCommand = moCObject.GetProcedure(CommandType.SQLSelect)
        loComm.Connection = msqlConnection
        If psFilter.Length <> 0 Or psOrderBy.Length <> 0 Then
            'Add leading space to ensure you replace whole word.  I.e.  Name vs. CommonName
            If psFilter.Length > 0 Then psFilter = psFilter.Insert(0, " ")
            If psOrderBy.Length > 0 Then psOrderBy = psOrderBy.Insert(0, " ")


        End If

        If psFilter.Length > 0 Then
            If InStr(loComm.CommandText.ToUpper, " WHERE ") = 0 Then
                loComm.CommandText += " WHERE " & psFilter
            Else
                loComm.CommandText += " AND " & psFilter
            End If
        End If
        If psOrderBy.Length > 0 Then
            If InStr(loComm.CommandText, " ORDER BY ") = 0 Then
                loComm.CommandText += " ORDER BY " & psOrderBy
            Else
                loComm.CommandText += " AND " & psOrderBy
            End If
        End If
        Return loComm
    End Function
    Private Sub MakeSelect(ByVal psFilter As String, ByVal psOrderBy As String)



        Dim loDataTable As CDataTable = New CDataTable



        MakeDatatable(psFilter, psOrderBy)
        If mbReturnDataTable Then
            Exit Sub
        End If
        loDataTable = moCDataTable
        If loDataTable.Count = 0 Then
            moSingleObject = Nothing
        ElseIf loDataTable.Count >= 1 Then

            moSingleObject = CType(loDataTable(0), CObject)

        End If
    End Sub

    Private disposedValue As Boolean = False        ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then

            End If

            If msqlConnection.State <> ConnectionState.Closed Then
                msqlConnection.Close()
            End If
        End If
        Me.disposedValue = True
    End Sub

#Region " IDisposable Support "
    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class
